﻿create or replace package body pk_tool is

/******************************/
/** private functions section */
/******************************/
  function get_market_name(a_id number) return varchar2
  is 
     l_name varchar2(20);
  begin
     select m.name
     into l_name
     from t_market m
     where m.id = a_id;
     return l_name;
  exception
    when no_data_found then
      raise_application_error(-20002, 'There is no such market, sorry dude!');
  end;
  
/***************************************************************/
/*********** Public section ************************************/
/***************************************************************/ 

  /** deciding if the pair is profitable                                                               */
  /* returns K - relation of income to expenseaces in all amount of the market account                 */
  function deal_K(buy_deal_rate number, 
                 sell_deal_rate number, 
                 buy_market_fee number, 
                 sell_market_fee number, 
                 buy_market_comm_cur number, 
                 sell_market_comm_cur number, 
                 buy_market_comm_pct number, 
                 sell_market_comm_pct number, 
                 buying_cur_initial_amount number, 
                 selling_cur_initial_amount number
                 
  ) return number
  is
       l_mda       number := Least(buying_cur_initial_amount/sell_deal_rate,selling_cur_initial_amount); -- maximum deal amount
  begin
       return ((buy_deal_rate-sell_deal_rate) * l_mda) /
              ( l_mda * ((sell_market_fee+sell_market_comm_pct)*sell_deal_rate+(buy_deal_rate+buy_market_comm_pct)*buy_market_fee)/100+
              buy_market_comm_cur*sell_deal_rate+sell_market_comm_cur );
  end; 

  /** deciding if the pair is balancig               */
  /* return profit in %                              */
  function balanced_deal_profit(buy_deal_rate number, sell_deal_rate number, buy_market_fee number, sell_market_fee number) return number
  is
  begin
     RETURN ( (buy_deal_rate-sell_deal_rate) - (buy_deal_rate*buy_market_fee/100 + sell_deal_rate*sell_market_fee/100) ) / sell_deal_rate;
  end; 

  procedure get_deals_history(a_market_id number DEFAULT NULL)
  is
    l_load_id  number;
  begin
    if a_market_id is null then
      l_load_id  := seq_load_id.nextval;
      for l_rec in (select m.id from t_market m)
      loop
        get_deals_history(l_rec.id);
      end loop;
    else 
       l_load_id := seq_load_id.currval;
      -- selecting all currencies pairs
      for l_pair in (select p.cur_from, p.cur_to from t_pairs p)
      loop
        execute immediate
          'insert into t_deals_history(id_load, id_market, buy_sell, cur_from, rate, cur_to, amount)
          select :1, :2, t.buysell, :3, t.rate, :4, NVL(t.amount,0)
          from table(pk_'||get_market_name(a_market_id)||'.book('''||l_pair.cur_from||''','''||l_pair.cur_to||''')) t'
        USING l_load_id, a_market_id, l_pair.cur_from, l_pair.cur_to;
      end loop;
    end if;
  end;
  
  function find_out_new_profit(a_dt date default null, a_K number default 3) return TDealsProcArr pipelined
  is
     l_load_id      number;
  begin
     -- let's find load_id at particular date
    select max(h.id_load)
    into l_load_id
    from t_deals_history h
    where h.dt between nvl(a_dt, SYSDATE)- 1/24/60 and nvl(a_dt, SYSDATE)+ 1/24/60;
    -- ïåðåáåðåì âñå âîçìîæíûå âàðèàíòû ïàð â äàííîé çàãðóçêå
    for l_rec in (
    select h_buy.id as buy_id, 
           h_sell.id as sell_id,
           -- bussines columns
           pk_tool.balanced_deal_profit(buy_deal_rate => h_buy.rate, 
                                sell_deal_rate => h_sell.rate,  
                                buy_market_fee => m_buy.fee, 
                                sell_market_fee => m_sell.fee) as balanced_profit,
          pk_tool.deal_K(buy_deal_rate => h_buy.rate, 
                 sell_deal_rate => h_sell.rate, 
                 buy_market_fee => m_buy.fee, 
                 sell_market_fee => m_sell.fee, 
                 buy_market_comm_cur => c_buy.commision_cur, 
                 sell_market_comm_cur => c_sell.commision_cur, 
                 buy_market_comm_pct => c_buy.commision_prc, 
                 sell_market_comm_pct => c_sell.commision_prc, 
                 buying_cur_initial_amount => b_sell.init_balance, 
                 selling_cur_initial_amount => b_buy.init_balance) as dealk
     from t_deals_history h_buy,
          t_market m_buy,
          t_market_commision c_buy,
          t_market_balance b_buy,
          t_deals_history h_sell,
          t_market m_sell,
          t_market_commision c_sell,
          t_market_balance b_sell          
     where h_buy.id_load = l_load_id
       and h_buy.buy_sell = 'buy'
       and h_sell.id_load = l_load_id
       and h_sell.buy_sell = 'sell'
       -- markets joins
       and m_buy.id = h_buy.id_market
       and m_sell.id = h_sell.id_market
       --
       and m_sell.id = c_sell.id_market
       and c_sell.cur_code = h_sell.cur_from
       --
       and m_buy.id = c_buy.id_market
       and c_buy.cur_code = h_buy.cur_to
       -- balances joins
       and b_buy.id_market = m_buy.id
       and b_buy.cur_code = h_buy.cur_to
       --
       and b_sell.id_market = m_sell.id
       and b_sell.cur_code = h_sell.cur_from
       )loop
           pipe row(l_rec);
       end loop;
       

/*

         insert into t_deals_to_process 
         values(seq_deals_to_process.nextval, l_pair.buy_id,l_pair.sell_id, 1, l_profit, l_deal_amount);

         insert into t_deals_to_process 
         values(seq_deals_to_process.nextval, l_pair.buy_id,l_pair.sell_id, 2, l_profit, l_deal_amount);
*/
     return;
  end;
  
end pk_tool;
/
